package org.restorator.dao.implement;

import org.restorator.dao.base.ItemDao;
import org.restorator.dao.implement.engin.ItemRowMapper;
import org.restorator.dao.jdbc.AbstractJdbsTemplate;
import org.restorator.dao.jdbc.Enricher;
import org.restorator.dao.jdbc.PreparedSetter;
import org.restorator.dao.jdbc.SimpleResultSetExtractor;
import org.restorator.dao.tx.TransactionManager;
import org.restorator.entity.Item;
import org.restorator.exception.DataStoreException;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import org.restorator.entity.Category;

/**
 * @author Snisar Bogdan.
 */
public class ItemDaoImpl extends AbstractJdbsTemplate<Item, Integer> implements ItemDao {

    public ItemDaoImpl() { }

    private static final String SQL_SELECT_BY_ORDER_ID = "SELECT a.ITEM_ID, a.ITEM_PRICE, a.ITEM_DESCRIPTION, a.ITEM_NAME, c.CATHEGORY_NAME FROM ITEM a, CATHEGORY c WHERE c.CATHEGORY_ID = a.CATHEGORY_ID AND a.ITEM_ID IN (SELECT oi.ITEM_ID FROM ORDER_ITEM oi WHERE oi.ORDER_ID = ?);";

    private static final String SQL_SELECT_GET_ALL_ITEM = "SELECT a.ITEM_ID, a.ITEM_PRICE, " +
            "a.ITEM_DESCRIPTION, a.ITEM_NAME, c.CATHEGORY_NAME FROM ITEM a, CATHEGORY c " +
            "WHERE c.CATHEGORY_ID = a.CATHEGORY_ID";
    
    private static final String SQL_SELECT_GET_ITEM_BY_ID = "SELECT a.ITEM_ID, a.ITEM_PRICE, a.ITEM_DESCRIPTION, a.ITEM_NAME, c.CATHEGORY_NAME FROM ITEM a, CATHEGORY c WHERE c.CATHEGORY_ID = a.CATHEGORY_ID AND a.ITEM_ID = ?";
    
    private static final String SQL_SELECT_DELETE_ITEM = "DELETE FROM ITEM WHERE ITEM_ID = ?";
    
    private static final String SQL_UPDATE_ITEM =  "UPDATE ITEM SET ITEM_ID =?, CATHEGORY_ID = ?, ITEM_PRICE = ?, ITEM_DESCRIPTION = ?, ITEM_NAME = ?" +
            "WHERE ITEM_ID = ?";
    
    private static final String SQL_INSERT_ITEM = "INSERT INTO ITEM (CATHEGORY_ID, ITEM_PRICE, ITEM_DESCRIPTION, ITEM_NAME) VALUE (?, ?, ?, ?)";
    
    @Override
    @SuppressWarnings("unchecked")
    public List<Item> getItemsByOrderId(final int id) throws DataStoreException {
        return executePreparList(SQL_SELECT_BY_ORDER_ID,
                new SimpleResultSetExtractor(new ItemRowMapper()),
                Enricher.NULL,
                new PreparedSetter() {
                    @Override
                    public void setParameters(PreparedStatement ps) throws SQLException {
                        ps.setInt(1, id);
                    }
                });
    }

    @Override
    public List<Item> getItemByCategoria(int categoriaId) throws DataStoreException {
//        return executePreparList(SQL_SELECT_BY_ORDER_ID,
//                new SimpleResultSetExtractor(
//                        new ItemRowMapper()),
//                Enricher.NULL,
//                new PreparedSetter() {
//                    @Override
//                    public void setParameters(PreparedStatement ps) {
//                        ps.setInt(1, );
//                    }
//                });
        return null;
    }

    @Override
    @SuppressWarnings("unchecked")
    public Item get(final Integer id) throws DataStoreException{
        return executeParameter(SQL_SELECT_GET_ITEM_BY_ID,
                new SimpleResultSetExtractor(new ItemRowMapper()),
                Enricher.NULL,
                new PreparedSetter() {
                    @Override
                    public void setParameters(PreparedStatement ps) throws SQLException {
                        ps.setInt(1, id);
                    }
                });  //To change body of implemented methods use File | Settings | File Templates.
    }

    @Override
    @SuppressWarnings("unchecked")

    public Item update(final Item it) throws DataStoreException{
        CategoryDaoImpl cdi = new CategoryDaoImpl();
        final Category cat = cdi.getCatByName(it.getCategoryName());
                executeParameter(SQL_INSERT_ITEM,
                new SimpleResultSetExtractor(new ItemRowMapper()),
                Enricher.NULL,
                new PreparedSetter() {
                    @Override
                    public void setParameters(PreparedStatement ps) throws SQLException {
                        ps.setInt(1, it.getItemId());
                        ps.setInt(2, cat.getCategoryId());
                        ps.setDouble(3, it.getPrice());
                        ps.setString(4, it.getDescription());
                        ps.setString(5, it.getName());
                    }
                });
                return it;//To change body of implemented methods use File | Settings | File Templates.
    }

    @Override
    @SuppressWarnings("unchecked")
    public void delete(final Item deleted) throws DataStoreException{
        executeParameter(SQL_SELECT_DELETE_ITEM,
            new SimpleResultSetExtractor(new ItemRowMapper()),
            Enricher.NULL,
            new PreparedSetter() {
                @Override
                public void setParameters(PreparedStatement ps) throws SQLException {
                    ps.setInt(1, deleted.getItemId());
                }
            });//To change body of implemented methods use File | Settings | File Templates.
    }

    @Override
    public long count() {
        return 0;  //To change body of implemented methods use File | Settings | File Templates.
    }

    @Override
    @SuppressWarnings("unchecked")
    public Integer save(final Item it, Object... addParams) throws DataStoreException {
        CategoryDaoImpl cdi = new CategoryDaoImpl();
        final Category cat = cdi.getCatByName(it.getCategoryName());
        executeInsert(SQL_INSERT_ITEM,
                it,
                Enricher.NULL,
                new PreparedSetter() {
                    @Override
                    public void setParameters(PreparedStatement ps) throws SQLException {
                        ps.setInt(1, cat.getCategoryId());
                        ps.setDouble(2, it.getPrice());
                        ps.setString(3, it.getDescription());
                        ps.setString(4, it.getName());
                    }
                });
        return null;
    }

//    @Override
//    public Integer save(Item persist) {
//        return null;  //To change body of implemented methods use File | Settings | File Templates.
//    }

    @Override
    @SuppressWarnings("unchecked")
    public List<Item> getAll() throws DataStoreException {
        return list(SQL_SELECT_GET_ALL_ITEM,
                new SimpleResultSetExtractor(new ItemRowMapper()),
                Enricher.NULL);  //To change body of implemented methods use File | Settings | File Templates.
    }

    @Override
    public boolean exist(Integer integer) {
        return false;  //To change body of implemented methods use File | Settings | File Templates.
    }

}
